﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CorporateGym.DAL.CorporateGymDBTableAdapters;
using GymWorkerRow = CorporateGym.DAL.CorporateGymDB.GymWorkerRow;
using MalfunctionRow = CorporateGym.DAL.CorporateGymDB.MalfunctionRow;
using MediaclApprovalRow = CorporateGym.DAL.CorporateGymDB.MedicalApprovalRow;
using MessageRow = CorporateGym.DAL.CorporateGymDB.MessageRow;
using MessageReceiverRow = CorporateGym.DAL.CorporateGymDB.MessageReceiverRow;
using ShiftRow = CorporateGym.DAL.CorporateGymDB.ShiftRow;
using TraineeRow = CorporateGym.DAL.CorporateGymDB.TraineeRow;
using TraineeVisitRow = CorporateGym.DAL.CorporateGymDB.TraineeVisitRow;
using PaymentsRow = CorporateGym.DAL.CorporateGymDB.PaymentsRow;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace CorporateGym.DAL
{
    public class DataAccess
    {
        #region SingleTone Implemetation
        private static readonly DataAccess _this;

        static DataAccess()
        {
            _this = new DataAccess();
        }

        public static DataAccess Instance { get { return _this; } }

        private DataAccess()
        {

        }
        #endregion

        #region members
        private bool isInitialized = false;
        private GymWorkerTableAdapter gymWorkerTableAdapter;
        private TraineeTableAdapter traineeTableAdapter;
        private MalfunctionTableAdapter malfunctionTableAdapter;
        private MedicalApprovalTableAdapter medicalApprovalTableAdapter;
        private MessageTableAdapter messageTableAdapter;
        private MessageReceiverTableAdapter messageReceiverTableAdapter;
        private ShiftTableAdapter shiftTableAdapter;
        private TraineeTypeTableAdapter traineeTypeTableAdapter;
        private TraineeVisitTableAdapter traineeVisitTableAdapter;
        private PaymentsTableAdapter paymentsTableAdapter;
        public CorporateGymDB CorporateGymDataSet { get; private set; }
        #endregion

        public void Init()
        {
            try
            {
                CorporateGymDataSet = new CorporateGymDB();
                gymWorkerTableAdapter = new GymWorkerTableAdapter();
                traineeTableAdapter = new TraineeTableAdapter();
                malfunctionTableAdapter = new MalfunctionTableAdapter();
                medicalApprovalTableAdapter = new MedicalApprovalTableAdapter();
                messageTableAdapter = new MessageTableAdapter();
                messageReceiverTableAdapter = new MessageReceiverTableAdapter();
                shiftTableAdapter = new ShiftTableAdapter();
                traineeTypeTableAdapter = new TraineeTypeTableAdapter();
                traineeVisitTableAdapter = new TraineeVisitTableAdapter();
                paymentsTableAdapter = new PaymentsTableAdapter();

                gymWorkerTableAdapter.Fill(CorporateGymDataSet.GymWorker);
                traineeTableAdapter.Fill(CorporateGymDataSet.Trainee);
                malfunctionTableAdapter.Fill(CorporateGymDataSet.Malfunction);
                medicalApprovalTableAdapter.Fill(CorporateGymDataSet.MedicalApproval);
                messageTableAdapter.Fill(CorporateGymDataSet.Message);
                messageReceiverTableAdapter.Fill(CorporateGymDataSet.MessageReceiver);
                shiftTableAdapter.Fill(CorporateGymDataSet.Shift);
                traineeTypeTableAdapter.Fill(CorporateGymDataSet.TraineeType);
                traineeVisitTableAdapter.Fill(CorporateGymDataSet.TraineeVisit);
                paymentsTableAdapter.Fill(CorporateGymDataSet.Payments);

                isInitialized = true;
            }
            catch (Exception ex)
            {

                //TODO: throw ex or return bool value to indicate if succeed
                //Faild
            }
        }

        public void SaveData()
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            try
            {
                gymWorkerTableAdapter.Update(CorporateGymDataSet);
                malfunctionTableAdapter.Update(CorporateGymDataSet);
                medicalApprovalTableAdapter.Update(CorporateGymDataSet);
                messageTableAdapter.Update(CorporateGymDataSet);
                messageReceiverTableAdapter.Update(CorporateGymDataSet);
                shiftTableAdapter.Update(CorporateGymDataSet);
                traineeTableAdapter.Update(CorporateGymDataSet);
                traineeVisitTableAdapter.Update(CorporateGymDataSet);
                paymentsTableAdapter.Update(CorporateGymDataSet);

            }
            catch (Exception ex)
            {
                //TODO: throw ex or return bool value to indicate if succeed
                //Faild
            }
        }

        #region worker methods Implementation
        public GymWorkerRow GetGymWorkerById(int i_WorkerId)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            GymWorkerRow gymWorkerRow = null;
            DataRow[] dataRows = CorporateGymDataSet.GymWorker.Select("WorkerId=" + i_WorkerId);
            if (dataRows != null && dataRows.Length == 1)
                gymWorkerRow = dataRows[0] as GymWorkerRow;
            
            return gymWorkerRow;
        }

        public GymWorkerRow GetGymWorkerByUserName(string i_UserName)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            GymWorkerRow gymWorkerRow = null;
            DataRow[] dataRows = CorporateGymDataSet.GymWorker.Select(string.Format("UserName='{0}'", i_UserName));
            if (dataRows != null && dataRows.Length == 1)
                gymWorkerRow = dataRows[0] as GymWorkerRow;

            return gymWorkerRow;
        }

        public GymWorkerRow GetGymWorkerMailAdress(string i_MailAdress)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            GymWorkerRow gymWorkerRow = null;
            DataRow[] dataRows = CorporateGymDataSet.GymWorker.Select(string.Format("Email='{0}'", i_MailAdress));
            if (dataRows != null && dataRows.Length == 1)
                gymWorkerRow = dataRows[0] as GymWorkerRow;

            return gymWorkerRow;
        }

        public List<GymWorkerRow> GetAllWorkers()
        {
            List<GymWorkerRow> allWorkers = new List<GymWorkerRow>();

            DataRow[] dataRows = CorporateGymDataSet.GymWorker.Select();
            foreach (GymWorkerRow worker in dataRows)
            {
                allWorkers.Add(worker);    
            }

            return allWorkers;
        }

        public int AddWorker(string i_FirstName, string i_LastName, string i_Phone, bool i_IsAdministrator, string i_UserName, string i_Passowrd, string i_Email)
        {
            GymWorkerRow gymWorkerRow = CorporateGymDataSet.GymWorker.NewGymWorkerRow();
            int newWorkerId;

            gymWorkerRow.FirstName = i_FirstName;
            gymWorkerRow.LastName = i_LastName;
            gymWorkerRow.Phone = i_Phone;
            gymWorkerRow.UserName = i_UserName;
            gymWorkerRow.Email = i_Email;
            /*
            //TODO need to move this to the logic layer
            System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
            Stream ms = GenerateStreamFromString(i_Passowrd);
            byte[] hashedPassword = md5.ComputeHash(ms);
            */
            gymWorkerRow.Password = i_Passowrd;
            gymWorkerRow.isAdministrator = i_IsAdministrator;
            
            try
            {
                CorporateGymDataSet.GymWorker.AddGymWorkerRow(gymWorkerRow);
                newWorkerId = gymWorkerRow.WorkerId;
            }
            catch (Exception ex)
            {
                newWorkerId = 0;
            }

            return newWorkerId;
        }

        public bool UpdateWorker(int i_id, string i_FirstName, string i_LastName, string i_Phone, bool i_IsAdministrator, string i_UserName, string i_Passowrd, string email)
        {
            GymWorkerRow gymWorkerRow = GetGymWorkerById(i_id);
            bool updateSucceeded = false;
            if (gymWorkerRow != null)
            {
                gymWorkerRow.FirstName = i_FirstName;
                gymWorkerRow.LastName = i_LastName;
                gymWorkerRow.Phone = i_Phone;
                gymWorkerRow.isAdministrator = i_IsAdministrator;
                gymWorkerRow.UserName = i_UserName;
                gymWorkerRow.Password = i_Passowrd;
                gymWorkerRow.Email = email;
                try
                {
                    if (gymWorkerTableAdapter.Update(gymWorkerRow) > 0)
                    {
                        updateSucceeded = true;
                    }
                }
                catch (Exception ex)
                {
                    updateSucceeded = false;
                }
                
            }

            return updateSucceeded;
        }

        public bool DeleteGymWorkerById(int i_Id)
        {
            bool deletionSuccees = false;

            GymWorkerRow gymWorker = GetGymWorkerById(i_Id);
            if (gymWorker != null)
            {
                try
                {
                    gymWorker.Delete();
                    deletionSuccees = true;
                }
                catch (Exception ex)
                {
                    deletionSuccees = false;
                }
            }

            return deletionSuccees;
        }

        public int CheckUserNamePassword(string i_UserName, string i_Password)
        {
            int workerId = -1;
            
            DataRow[] dataRows = CorporateGymDataSet.GymWorker.Select(string.Format("UserName='{0}'", i_UserName));
            if (dataRows != null && dataRows.Length == 1)
            {
                string password = (dataRows[0] as GymWorkerRow).Password;
                if (i_Password == password)
                {
                    workerId = (dataRows[0] as GymWorkerRow).WorkerId;
                }
            }

            return workerId;
        }

        private bool isUserNameExists(string i_UserName)
        {
            bool notExists = false;

            DataRow[] dataRows = CorporateGymDataSet.GymWorker.Select("UserName=" + i_UserName);
            if (dataRows == null)
            {
                notExists = true;
            }

            return notExists;
        }

        #endregion

        #region malfunction methods Implemetation
        public MalfunctionRow GetMafunctionById(int i_Id)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            MalfunctionRow malfunctionRow = null;
            DataRow[] dataRows = CorporateGymDataSet.Malfunction.Select("MalfunctionId=" + i_Id);
            if (dataRows != null && dataRows.Length == 1)
                malfunctionRow = dataRows[0] as MalfunctionRow;

            return malfunctionRow;
        }
        /// <summary>
        /// Get All Mulfunctions by status
        /// </summary>
        /// <param name="i_Status">
        /// 0 - all malfunction
        /// 1 - only open
        /// 2 - only closed
        /// </param>
        /// <returns></returns>
        public List<MalfunctionRow> GetAllMalfunctions(int i_Status)
        {
            List<MalfunctionRow> allMalfunctions = new List<MalfunctionRow>();
            DataRow[] dataRows = null;
            switch (i_Status)
            {
                case 0:
                    dataRows = CorporateGymDataSet.Malfunction.Select();
                    break;
                case 1:
                    dataRows = CorporateGymDataSet.Malfunction.Select("CloserId = -1");
                    break;
                case 2:
                    dataRows = CorporateGymDataSet.Malfunction.Select("CloserId > -1");
                    break;
                default:
                    break;
            }
            
            foreach (MalfunctionRow malfunction in dataRows)
            {
                allMalfunctions.Add(malfunction);
            }

            return allMalfunctions;
        }

        public int AddMalfunction(string i_Name, string i_Description, DateTime i_CreateTime, int i_CreatorId)
        {
            MalfunctionRow malfunctionRow = CorporateGymDataSet.Malfunction.NewMalfunctionRow();
            int newMalfunctionId;

            malfunctionRow.Name = i_Name;
            malfunctionRow.Description = i_Description;
            malfunctionRow.CreateTime = i_CreateTime;
            malfunctionRow.CreatorId = i_CreatorId;

            try
            {
                CorporateGymDataSet.Malfunction.AddMalfunctionRow(malfunctionRow);
                newMalfunctionId = malfunctionRow.MalfunctionId;
            }
            catch (Exception ex)
            {
                newMalfunctionId = -1;
            }

            return newMalfunctionId;
        }

        public bool CloseMalfunction(int i_Id, int i_CloserId)
        {
            bool closingSucceed = false;
            MalfunctionRow malfunctionRow = null;

            DataRow[] dataRows = CorporateGymDataSet.Malfunction.Select("MalfunctionId=" + i_Id);
            if (dataRows != null && dataRows.Length == 1)
            {
                malfunctionRow = dataRows[0] as MalfunctionRow;
                malfunctionRow.CloserId = i_CloserId;
                if (malfunctionRow.IscloseTimeNull())
                {
                    malfunctionRow.closeTime = DateTime.Now;
                    try
                    {
                        //CorporateGymDataSet.Malfunction.AddMalfunctionRow(malfunctionRow);
                        closingSucceed = true;
                    }
                    catch (Exception ex)
                    { 
                        //TODO
                    }
                    
                }
            }

            return closingSucceed;
        }
        #endregion

        #region Medical Approval Implementation
        public MediaclApprovalRow GetMediaclApprovalById(int i_Id)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            MediaclApprovalRow approval = null;
            DataRow[] dataRows = CorporateGymDataSet.MedicalApproval.Select("ApprovalId=" + i_Id);
            if (dataRows != null && dataRows.Length == 1)
                approval = dataRows[0] as MediaclApprovalRow;

            return approval;
        }

        public int AddMedicalApproval(DateTime i_ExpirationDate)
        {
            MediaclApprovalRow medicalApprovalRow = CorporateGymDataSet.MedicalApproval.NewMedicalApprovalRow();
            int newApprovalId;

            medicalApprovalRow.ExpirationDate = i_ExpirationDate;

            try
            {
                CorporateGymDataSet.MedicalApproval.AddMedicalApprovalRow(medicalApprovalRow);
                newApprovalId = medicalApprovalRow.ApprovalId;
            }
            catch (Exception ex)
            {
                newApprovalId = 0;
            }

            return newApprovalId;
        }

        public bool UpdateMedicalApprovalExpirationDate(int i_Id, DateTime i_NewExpirationDate)
        {
            MediaclApprovalRow approvalRow = GetMediaclApprovalById(i_Id);
            bool updateSucceeded = false;
            if (approvalRow != null)
            {
                approvalRow.ExpirationDate = i_NewExpirationDate;
                try
                {
                    if (medicalApprovalTableAdapter.Update(approvalRow) > 0)
                    {
                        updateSucceeded = true;
                    }
                }
                catch (Exception ex)
                {
                    updateSucceeded = false;
                }

            }

            return updateSucceeded;
        }
        #endregion

        #region Message Implementation
        public List<MessageRow> GetMessageBoardMessageRow()
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            List<MessageRow> messageBoardMessages = new List<MessageRow>();

            DataRow[] dataRows = CorporateGymDataSet.Message.Select("IsMessageBoard = 1");

            foreach (DataRow dataRow in dataRows)
            {
                MessageRow messageRow = dataRow as MessageRow;
                messageBoardMessages.Add(messageRow);
            }

            return messageBoardMessages;
        }

        public List<GymWorkerRow> GetMessageRecievers(int messageId)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            List<GymWorkerRow> messageReceivers = new List<GymWorkerRow>();

            DataRow[] dataRows = CorporateGymDataSet.MessageReceiver.Select("MessageId=" + messageId);

            foreach (DataRow dataRow in dataRows)
            {
                MessageReceiverRow messageReceiverRow = dataRow as MessageReceiverRow;
                GymWorkerRow gymWorker = GetGymWorkerById(messageReceiverRow.ReceiverId);
                messageReceivers.Add(gymWorker);
            }

            return messageReceivers;
        }

        public void AddMessageboardMessage(int parentId, int workerId, DateTime creationTime, string messageSubject, string messageContent, bool isMessageBoard)
        {
            MessageRow messageRow = CorporateGymDataSet.Message.NewMessageRow();

            messageRow.ParentMessageId = parentId;
            messageRow.SenderId = workerId;
            messageRow.CreateTime = creationTime;
            messageRow.Subject = messageSubject;
            messageRow.Text = messageContent;
            messageRow.IsMessageBoard = isMessageBoard;

            CorporateGymDataSet.Message.AddMessageRow(messageRow);
        }
        
        public int AddMessage(int i_ParentMessageId, int i_SenderId, DateTime i_CreateTime, string i_Subject, string i_Text, List<int> i_Receivers, bool i_IsMessageBoard)
        {
            MessageRow messageRow = CorporateGymDataSet.Message.NewMessageRow();
            int newMessageId;

            messageRow.ParentMessageId = i_ParentMessageId;
            messageRow.SenderId = i_SenderId;
            messageRow.CreateTime = i_CreateTime;
            messageRow.Subject = i_Subject;
            messageRow.Text = i_Text;
            messageRow.IsMessageBoard = i_IsMessageBoard;

            try
            {
                CorporateGymDataSet.Message.AddMessageRow(messageRow);
                newMessageId = messageRow.MessageId;
                foreach(int receiver in i_Receivers)
                {
                    AddMessageReceiver(newMessageId, receiver);
                }
            }
            catch (Exception ex)
            {
                newMessageId = 0;
            }

            return newMessageId;
        }

        public bool AddMessageReceiver(int i_MessageId, int i_ReceiverId)
        {
            MessageReceiverRow messageReceiverRow = CorporateGymDataSet.MessageReceiver.NewMessageReceiverRow();
            bool insertSuccess;
            messageReceiverRow.MessageId = i_MessageId;
            messageReceiverRow.ReceiverId = i_ReceiverId;
            messageReceiverRow.IsRead = false;

            try
            {
                CorporateGymDataSet.MessageReceiver.AddMessageReceiverRow(messageReceiverRow);
                insertSuccess = true;
            }
            catch (Exception ex)
            {
                insertSuccess = false;
                System.Console.WriteLine(ex.StackTrace.ToString());
            }

            return insertSuccess;
        }

        public MessageRow GetMessageById(int i_Id)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            MessageRow messageRow = null;
            DataRow[] dataRows = CorporateGymDataSet.Message.Select("MessageId=" + i_Id);
            if (dataRows != null && dataRows.Length == 1)
                messageRow = dataRows[0] as MessageRow;
            
            return messageRow;
        }

        public List<MessageRow> GetAllMessagesToWorker(int i_WorkerId)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            List<int> messagesIds = new List<int>();
            List<MessageRow> messages = new List<MessageRow>();

            DataRow[] dataRows = CorporateGymDataSet.MessageReceiver.Select("ReceiverId=" + i_WorkerId);
            foreach(DataRow row in dataRows)
            {
                MessageReceiverRow messageRow = row as MessageReceiverRow;
                messagesIds.Add(messageRow.MessageId);
            }

            foreach (int messageId in messagesIds)
            {
                messages.Add(GetMessageById(messageId));
            }

            return messages;
        }

        public List<MessageRow> GetAllMessagesFromWorker(int i_WorkerId)
        {
            if (isInitialized == false)
                throw new Exception("DB was not initialized!!!");

            List<MessageRow> messages = new List<MessageRow>();

            DataRow[] dataRows = CorporateGymDataSet.Message.Select("SenderId=" + i_WorkerId + "AND IsMessageBoard=0");
            foreach (DataRow row in dataRows)
            {
                MessageRow messageRow = row as MessageRow;
                messages.Add(messageRow);
            }

            return messages;
        }

        public MessageRow GetParentMessageByMessageId()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region shifts implementation
        public bool AddShift (int i_GymWorkerId, DateTime i_Start, DateTime i_End)
        {
            bool addingSucceed = false;

            ShiftRow shift = CorporateGymDataSet.Shift.NewShiftRow();
            shift.WorkerId = i_GymWorkerId;
            shift.StraingHour = i_Start;
            shift.EndTime = i_End;
            
            try
            {
                CorporateGymDataSet.Shift.AddShiftRow(shift);
                addingSucceed = true;
            }
            catch
            {
                
            }

            return addingSucceed;
        }

        public List<ShiftRow> GetShiftsByGymWorkerId(int i_GymWorkerId)
        {
            List<ShiftRow> shifts = new List<ShiftRow>();

            string command = "WorkerId = " + i_GymWorkerId + " and EndTime is not null";
            DataRow[] rows = CorporateGymDataSet.Shift.Select(command);
            if (rows != null)
            {
                foreach (DataRow row in rows)
                {
                    shifts.Add(row as ShiftRow);
                }
            }
            return shifts;
        }
        #endregion

        #region trainee implementation
        public TraineeRow GetTraineeById(int i_TraineeId)
        {
            TraineeRow traineeRow = null;
            DataRow[] dataRows = CorporateGymDataSet.Trainee.Select("TraineeId=" + i_TraineeId);
            if (dataRows != null && dataRows.Length == 1)
                traineeRow = dataRows[0] as TraineeRow;

            return traineeRow;
        }

        public List<TraineeRow> GetAllTrainees(string i_Search)
        {
            List<TraineeRow> allTrainees = new List<TraineeRow>();
            int stringLen = i_Search.Length;
            string query = string.Format(
                "FirstName like '{0}%' or LastName like '{0}%' or Phone like '{0}%' or Email like '{0}%' or WorkerId like '{0}%'",
                i_Search, stringLen);
            DataRow[] dataRows = CorporateGymDataSet.Trainee.Select(query);
            foreach (TraineeRow trainee in dataRows)
            {
                allTrainees.Add(trainee);
            }

            return allTrainees;
        }

        public bool DeleteTraineeById(int i_TraineeId)
        {

            bool deletionSuccees = false;

            TraineeRow traineeRow = GetTraineeById(i_TraineeId);
            if (traineeRow != null)
            {
                try
                {
                    traineeRow.Delete();
                    deletionSuccees = true;
                }
                catch (Exception ex)
                {
                    deletionSuccees = false;
                }
            }

            return deletionSuccees;
        }

        public int AddTrainee(string i_WorkerId, string i_FirstName, string i_LastName, string i_Phone, string i_Email, int i_WorkerType, int i_MedicalApprovalId)
        {
            int newTraineeId = 0;
            TraineeRow newTraineeRow = CorporateGymDataSet.Trainee.NewTraineeRow();

            newTraineeRow.WorkerId = i_WorkerId;
            newTraineeRow.FirstName = i_FirstName;
            newTraineeRow.LastName = i_LastName;
            newTraineeRow.Phone = i_Phone;
            newTraineeRow.Email = i_Email;
            newTraineeRow.WorkerType = i_WorkerType;
            newTraineeRow.MedicalApprovalId = i_MedicalApprovalId;
            newTraineeRow.AbsenseNotificationSent = false;

            try
            {
                CorporateGymDataSet.Trainee.AddTraineeRow(newTraineeRow);
                newTraineeId = newTraineeRow.TraineeId;
            }
            catch (Exception ex)
            { 
                
            }

            return newTraineeId;
        }


        public bool UpdateTrainee(int i_id, string i_WorkerId, string i_FirstName, string i_LastName, string i_Phone, string i_Email, int i_WorkerType, int i_MedicalApprovalId)
        {
            TraineeRow traineeRow = GetTraineeById(i_id);
            bool updateSucceeded = false;
            if (traineeRow != null)
            {
                traineeRow.FirstName = i_FirstName;
                traineeRow.LastName = i_LastName;
                traineeRow.Phone = i_Phone;
                traineeRow.WorkerId = i_WorkerId;
                traineeRow.WorkerType = i_WorkerType;
                traineeRow.Email = i_Email;
                traineeRow.MedicalApprovalId = i_MedicalApprovalId;
                try
                {
                    if (traineeTableAdapter.Update(traineeRow) > 0)
                    {
                        updateSucceeded = true;
                    }
                }
                catch (Exception ex)
                {
                    updateSucceeded = false;
                }

            }

            return updateSucceeded;
        }

        public void AddTraineeVisit(int i_TraineeId)
        {
            TraineeVisitRow newVisit = CorporateGymDataSet.TraineeVisit.NewTraineeVisitRow();

            newVisit.TraineeId = i_TraineeId;
            newVisit.StratingHour = DateTime.Now;

            CorporateGymDataSet.TraineeVisit.AddTraineeVisitRow(newVisit);

        }


        public TraineeVisitRow GetLastVisitingByTraineeId(int i_TraineeId)
        {
            TraineeVisitRow result = null;
            DataRow[] rows = CorporateGymDataSet.TraineeVisit.Select("TraineeId = " + i_TraineeId, "StratingHour Desc");
            if (rows != null && rows.Length >= 1)
            {
                result =  rows[0] as TraineeVisitRow;
            }

            return result;
        }

        public TraineeVisitRow[] GetVisitsByTraineeId(int i_Id)
        {
            DataRow[] rows = CorporateGymDataSet.TraineeVisit.Select("TraineeId = " + i_Id);
            return rows as TraineeVisitRow[];
        }

        #endregion

        #region payments
        public bool AddPayment(int i_TraineeId, int i_Month, int i_Year)
        {
            bool addingSucceed = false;

            PaymentsRow payment = CorporateGymDataSet.Payments.NewPaymentsRow();
            payment.TraineeId = i_TraineeId;
            payment.Month = i_Month;
            payment.Year = i_Year;
                
            CorporateGymDataSet.Payments.AddPaymentsRow(payment);
            addingSucceed = true;
            


            return addingSucceed;
        }

        public PaymentsRow GetPayment(int i_userId, int i_monthNumber, int i_Year)
        {
            PaymentsRow res = null;

            DataRow[] paymentRows = CorporateGymDataSet.Payments.Select(string.Format("TraineeId='{0}' AND Month='{1}' AND Year='{2}' ", i_userId, i_monthNumber, i_Year));
            if (paymentRows.Length > 0)
            {
                res = paymentRows[0] as PaymentsRow;
            }

            return res;
        }

        public PaymentsRow[] GetTraineePaymentHistory(int i_traineeId)
        {
            List<String> paymentsHistoryList = new List<string>();
            DataRow[] paymentRows = CorporateGymDataSet.Payments.Select(string.Format("TraineeId='{0}'", i_traineeId));

            return paymentRows as PaymentsRow[];
        }

        public void RemovePayment(int i_TraineeId, int i_Month, int i_Year)
        {
            PaymentsRow paymentRow = GetPayment(i_TraineeId, i_Month, i_Year);
            CorporateGymDataSet.Payments.RemovePaymentsRow(paymentRow);
        }
        public bool AbsenseNotification(int i_TraineeId, bool i_NewVal)
        {
            bool updateSucceeded = false;
            TraineeRow row =  GetTraineeById(i_TraineeId);
            row.AbsenseNotificationSent = i_NewVal;

            try
            {
                if (traineeTableAdapter.Update(row) > 0)
                {
                    updateSucceeded = true;
                }
            }
            catch (Exception ex)
            {
                updateSucceeded = false;
            }

            return updateSucceeded;
        }
        #endregion

        public bool ChangePassword(string i_UserName, string i_Old, string i_New)
        {
            bool isChanged = false;
            
            GymWorkerRow row = GetGymWorkerByUserName(i_UserName);
            if (row != null && i_Old.Equals(row.Password))
            {
                row.Password = i_New;
                try
                {
                    if (gymWorkerTableAdapter.Update(row) > 0)
                    {
                        isChanged = true;
                    }
                }
                catch (Exception ex)
                {
                    isChanged = false;
                }
            }

            return isChanged;
        }
    }
}
